A Hands-On Tour Inside the World of PROC SQL.PDF

Structured Query Language (PROC SQL) is a database language found in the base-SAS software. It enables access to data stored in SAS data sets or tables using a powerful assortment of statements, clauses, options, functions, and other language features. This hands -on workshop presents core concepts of this powerful language as well as its many applications, and is intended for SAS users who desire an overview of the capabilities of this exciting procedure. Attendees will explore the construction of simple SQL queries, ordering and grouping data, the application of case logic for data reclassification, the creation and use of views, and the construction of simple inner and outer joins. Introduction The SQL procedure is a wonderful tool for querying and subsetting data; restructuring or regrouping data by specifying case expressions ; constructing and using virtual tables known as view; joining two or more tables (up to 32) to explore data relationships . Occasionally, a problem comes along where the SQL procedure is either better suited or easier to use than other more conventional DATA and/or PROC step methods. As each situation presents itself, PROC SQL should be examined to see if its use is warranted for the task at hand. PROC SQL proponents frequently use the power and coding simplicity of the SQL procedure to perform an assortment of common everyday tasks to those requiring a highly complex and analytical approach. Whatever the nature of SQL usage, this paper is intended to present an overview of some of the most exciting features found in PROC SQL. Example Tables The examples used throughout this paper utilize a database of tables. A relational database is a collection of tables. Each table contains one or more columns and one or more rows of data. The example database consists of three tables: CUSTOMERS, MOVIES, and ACTORS. Each table appears below. CUSTOMERS CUST_NO NAME CITY STATE 11321 John Smith Miami FL 44555 Alice Jones Baltimore MD 21713 Ryan Adams Atlanta GA MOVIES CUST_NO MOVIE_NO TITLE RATING CATEGORY 44555 1011 Brave Heart R Action Adventure 21713 3090 Rocky PG Drama 44555 2198 Christmas Vacation PG-13 Comedy 37753 4456 Silence of the Lambs R Suspense ACTORS MOVIE_NO LEADING_ACTOR 1011 Mel Gibson 2198 Chevy Chase 3090 Sylvester Stallone